﻿Imports System.Collections.Generic
Imports System.Collections.ObjectModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.Script.Serialization


Public Class jqGridHandler
    Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim request As HttpRequest = context.Request
        Dim response As HttpResponse = context.Response

        Dim _search As String = request("_search")
        Dim numberOfRows As String = request("rows")
        Dim pageIndex As String = request("page")
        Dim sortColumnName As String = request("sidx")
        Dim sortOrderBy As String = request("sord")


        Dim totalRecords As Integer
        Dim users As Collection(Of User) = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, totalRecords)
        Dim output As String = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords))
        response.Write(output)
    End Sub

    Private Function BuildJQGridResults(ByVal users As Collection(Of User), ByVal numberOfRows As Integer, ByVal pageIndex As Integer, ByVal totalRecords As Integer) As String

        Dim result As New JQGridResults()
        Dim rows As New List(Of JQGridRow)()
        For Each user As User In users
            Dim row As New JQGridRow()
            row.id = user.UserID
            row.cell = New String(5) {}
            row.cell(0) = user.UserID.ToString()
            row.cell(1) = user.UserName
            row.cell(2) = user.FirstName
            row.cell(3) = user.MiddleName
            row.cell(4) = user.LastName
            row.cell(5) = user.EmailID
            rows.Add(row)
        Next
        result.rows = rows.ToArray()
        result.page = pageIndex
        result.total = totalRecords \ numberOfRows
        result.records = totalRecords
        Return New JavaScriptSerializer().Serialize(result)
    End Function

    Private Function GetUsers(ByVal numberOfRows As String, ByVal pageIndex As String, ByVal sortColumnName As String, ByVal sortOrderBy As String, ByRef totalRecords As Integer) As Collection(Of User)
        Dim users As New Collection(Of User)()
        Dim connectionString As String = "Data Source=MURALI\SA; Initial Catalog=PhilaDB; User ID=sa; Password=sql1234"
        Using connection As New SqlConnection(connectionString)
            Using command As New SqlCommand()
                command.Connection = connection
                command.CommandText = "SelectjqGridUsers"
                command.CommandType = CommandType.StoredProcedure

                Dim paramPageIndex As New SqlParameter("@PageIndex", SqlDbType.Int)
                paramPageIndex.Value = Convert.ToInt32(pageIndex)
                command.Parameters.Add(paramPageIndex)

                Dim paramColumnName As New SqlParameter("@SortColumnName", SqlDbType.VarChar, 50)
                paramColumnName.Value = sortColumnName
                command.Parameters.Add(paramColumnName)

                Dim paramSortorderBy As New SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4)
                paramSortorderBy.Value = sortOrderBy
                command.Parameters.Add(paramSortorderBy)

                Dim paramNumberOfRows As New SqlParameter("@NumberOfRows", SqlDbType.Int)
                paramNumberOfRows.Value = Convert.ToInt32(numberOfRows)
                command.Parameters.Add(paramNumberOfRows)

                Dim paramTotalRecords As New SqlParameter("@TotalRecords", SqlDbType.Int)
                totalRecords = 0
                paramTotalRecords.Value = totalRecords
                paramTotalRecords.Direction = ParameterDirection.Output
                command.Parameters.Add(paramTotalRecords)


                connection.Open()
                Using dataReader As SqlDataReader = command.ExecuteReader()
                    Dim user As User
                    While dataReader.Read()
                        user = New User()
                        user.UserID = CInt(dataReader("UserID"))
                        user.UserName = Convert.ToString(dataReader("UserName"))
                        user.FirstName = Convert.ToString(dataReader("FirstName"))
                        user.MiddleName = Convert.ToString(dataReader("MiddleName"))
                        user.LastName = Convert.ToString(dataReader("LastName"))
                        user.EmailID = Convert.ToString(dataReader("EmailID"))
                        users.Add(user)
                    End While
                End Using
                totalRecords = CInt(paramTotalRecords.Value)
            End Using

            Return users
        End Using

    End Function
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        ' To enable pooling, return true here.
        ' This keeps the handler in memory.
        Get
            Return False
        End Get
    End Property



    Public Structure JQGridResults
        Public page As Integer
        Public total As Integer
        Public records As Integer
        Public rows As JQGridRow()

    End Structure
    Public Structure JQGridRow
        Public id As Integer
        Public cell As String()
    End Structure

    <Serializable()> _
    Public Class User
        Public Property UserID() As Integer
            Get
                Return m_UserID
            End Get
            Set(ByVal value As Integer)
                m_UserID = Value
            End Set
        End Property
        Private m_UserID As Integer

        Public Property UserName() As String
            Get
                Return m_UserName
            End Get
            Set(ByVal value As String)
                m_UserName = Value
            End Set
        End Property
        Private m_UserName As String

        Public Property FirstName() As String
            Get
                Return m_FirstName
            End Get
            Set(ByVal value As String)
                m_FirstName = Value
            End Set
        End Property
        Private m_FirstName As String

        Public Property MiddleName() As String
            Get
                Return m_MiddleName
            End Get
            Set(ByVal value As String)
                m_MiddleName = Value
            End Set
        End Property
        Private m_MiddleName As String

        Public Property LastName() As String
            Get
                Return m_LastName
            End Get
            Set(ByVal value As String)
                m_LastName = Value
            End Set
        End Property
        Private m_LastName As String

        Public Property EmailID() As String
            Get
                Return m_EmailID
            End Get
            Set(ByVal value As String)
                m_EmailID = Value
            End Set
        End Property
        Private m_EmailID As String
    End Class



End Class
